﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Noris.Schedule.Support.Sql;
using System.Windows.Forms;

namespace Noris.Tools.FrxEditor.Data
{
    #region class SimpleDataTable : Tabulka obsahující data načtená z databáze.
    /// <summary>
    /// SimpleDataTable : Tabulka obsahující data načtená z databáze.
    /// Tabulku lze vytvořit pouze statickou metodou SimpleDataTable.Load(string sqlCommand).
    /// Struktura tabulky (sloupce) se vytvoří podle dat, která vrátí SQL příkaz.
    /// </summary>
    public class SimpleDataTable
    {
        #region Konstrukce
        /// <summary>
        /// Konstruktor je privátní
        /// </summary>
        private SimpleDataTable()
        {
            this._Clear();
        }
        /// <summary>
        /// Vrátí tabulku s daty podle daného selectu
        /// </summary>
        /// <param name="sqlCommand"></param>
        /// <returns></returns>
        public static SimpleDataTable Load(string sqlCommand)
        {
            SimpleDataTable table = new SimpleDataTable();
            table._Load(sqlCommand);
            return table;
        }
        /// <summary>
        /// Vizualizace
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            return "ColumnCount: " + this.ColumnCount.ToString() + "; RowCount: " + this.RowCount.ToString();
        }
        #endregion
        #region Public property
        /// <summary>
        /// SQL SELECT, který je v tabulce načten
        /// </summary>
        public string SqlCommand { get; private set; }
        /// <summary>
        /// Soupis sloupců. Je indexován podle pořadového čísla sloupce a podle jeho názvu.
        /// Nepřipouští se duplicitní názvy sloupců.
        /// </summary>
        public SimpleDataColumnCollection Columns { get; private set; }
        /// <summary>
        /// Počet sloupců
        /// </summary>
        public int ColumnCount { get { return (this._LoadedColumns != null ? this._LoadedColumns.Count : this.Columns.Count); } }
        /// <summary>
        /// Soupis řádků.
        /// </summary>
        public SimpleDataRowCollection Rows { get; private set; }
        /// <summary>
        /// Počet řádků
        /// </summary>
        public int RowCount { get { return (this._LoadedRows != null ? this._LoadedRows.Count : this.Rows.Count); } }
        #endregion
        #region Načítání dat pomocí DataReaderu
        private void _Load(string sqlSelect)
        {
            this._Clear();
            this._LoadedColumns = new List<SimpleDataColumn>();
            this._LoadedRows = new List<SimpleDataRow>();
            this.SqlCommand = sqlSelect;
            Db_Layer.ReadFromDataReader(sqlSelect, this._LoadColumns, this._LoadRow);
            this.Columns = new SimpleDataColumnCollection(this._LoadedColumns);
            this._LoadedColumns = null;
            this.Rows = new SimpleDataRowCollection(this._LoadedRows);
            this._LoadedRows = null;
        }
        private void _Clear()
        {
            this.SqlCommand = "";
            this.Columns = new SimpleDataColumnCollection();
            this.Rows = new SimpleDataRowCollection();
        }
        private void _LoadColumns(SqlDataReader reader)
        {
            for (int columnIndex = 0; columnIndex < reader.FieldCount; columnIndex++)
                this._LoadedColumns.Add(new SimpleDataColumn(this, columnIndex, reader.GetName(columnIndex), reader.GetFieldType(columnIndex)));
        }
        private bool _LoadRow(SqlDataReader reader)
        {
            this._LoadedRows.Add(new SimpleDataRow(this, reader));
            return true;
        }
        private List<SimpleDataColumn> _LoadedColumns;
        private List<SimpleDataRow> _LoadedRows;
        #endregion
        #region Získání dat sloupce, hodnoty z řádku a sloupce
        /// <summary>
        /// Zjistí, zda tato tabulka obsahuje sloupec daného názvu
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public bool ContainColumn(string columnName)
        {
            string key = columnName.ToLower();
            return this.Columns.ContainsColumn(key);
        }
        /// <summary>
        /// Zjistí, zda tato tabulka obsahuje sloupec daného indexu
        /// </summary>
        /// <param name="columnId"></param>
        /// <returns></returns>
        public bool ContainColumn(int columnId)
        {
            return this.Columns.ContainsColumn(columnId);
        }
        /// <summary>
        /// Vrátí sloupec podle jeho názvu.
        /// Pokud neexistuje, vyhodí chybu.
        /// </summary>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public SimpleDataColumn GetColumn(string columnName)
        {
            string key = columnName.ToLower();
            if (!this.Columns.ContainsColumn(key))
                throw new KeyNotFoundException("Column \"" + columnName + "\" does not exists in SimpleTable.");
            return this.Columns[key];
        }
        /// <summary>
        /// Vrátí sloupec podle jeho indexu.
        /// Pokud neexistuje, vyhodí chybu.
        /// </summary>
        /// <param name="columnId"></param>
        /// <returns></returns>
        public SimpleDataColumn GetColumn(int columnId)
        {
            if (!this.Columns.ContainsColumn(columnId))
                throw new KeyNotFoundException("Column with index " + columnId.ToString() + " does not exists in SimpleTable.");
            return this.Columns[columnId];
        }
        /// <summary>
        /// Vrátí hodnotu z daného řádku a sloupce
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="rowIndex"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public T Get<T>(int rowIndex, string columnName)
        {
            return this.Get<T>(rowIndex, GetColumn(columnName).ColumnIndex);
        }
        /// <summary>
        /// Vrátí hodnotu z daného řádku a sloupce
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="rowIndex"></param>
        /// <param name="columnIndex"></param>
        /// <returns></returns>
        public T Get<T>(int rowIndex, int columnIndex)
        {
            if (rowIndex < 0 || rowIndex >= this.RowCount)
                throw new IndexOutOfRangeException("Row index " + rowIndex.ToString() + " is out of range, RowCount = " + this.RowCount.ToString());
            return this.Rows[rowIndex].Get<T>(columnIndex);
        }
        #endregion
    }
    #endregion
    #region class SimpleDataColumnCollection : kolekce sloupců
    /// <summary>
    /// Kolekce sloupců
    /// </summary>
    public class SimpleDataColumnCollection : IEnumerable<SimpleDataColumn>
    {
        /// <summary>
        /// Konstruktor
        /// </summary>
        public SimpleDataColumnCollection()
        {
            this.Columns = new CollectIdx<SimpleDataColumn>();
        }
        /// <summary>
        /// Konstruktor
        /// </summary>
        /// <param name="columns"></param>
        public SimpleDataColumnCollection(IEnumerable<SimpleDataColumn> columns)
        {
            this.Columns = new CollectIdx<SimpleDataColumn>();
            if (columns != null)
                this.Columns.AddRange(columns);
        }
        /// <summary>
        /// Vizualizace
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            return "ColumnCollection of " + this.Count.ToString() + " columns";
        }
        private CollectIdx<SimpleDataColumn> Columns;
        private IEnumerable<SimpleDataColumn> IEnumerableColumns { get { return (IEnumerable<SimpleDataColumn>)this.Columns; } }
        #region Public property a metody
        /// <summary>
        /// Počet sloupců v kolekci
        /// </summary>
        public int Count { get { return this.Columns.Count; } }
        /// <summary>
        /// Vrátí sloupec podle jeho indexu
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        public SimpleDataColumn this[int index] { get { return this.Columns[index]; } }
        /// <summary>
        /// Vrátí sloupec podle jeho názvu
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public SimpleDataColumn this[string key] { get { return this.Columns[key]; } }
        /// <summary>
        /// Vrátí informaci, zda existuje sloupec daného indexu
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        public bool ContainsColumn(int index) { return this.Columns.ContainsKey(index); }
        /// <summary>
        /// Vrátí informaci, zda existuje sloupec daného názvu
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public bool ContainsColumn(string key) { return this.Columns.ContainsKey(key); }

        #endregion
        #region IEnumerable
        IEnumerator<SimpleDataColumn> IEnumerable<SimpleDataColumn>.GetEnumerator() { return this.IEnumerableColumns.GetEnumerator(); }
        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() { return this.IEnumerableColumns.GetEnumerator(); }
        #endregion
    }
    #endregion
    #region class SimpleDataColumn : Informace o jednom sloupci
    /// <summary>
    /// SimpleDataColumn : Informace o jednom sloupci
    /// </summary>
    public class SimpleDataColumn : IIdKey
    {
        /// <summary>
        /// Konstruktor
        /// </summary>
        /// <param name="table"></param>
        /// <param name="columnIndex"></param>
        /// <param name="columnName"></param>
        /// <param name="dataType"></param>
        public SimpleDataColumn(SimpleDataTable table, int columnIndex, string columnName, Type dataType)
        {
            this.Table = table;
            this.ColumnIndex = columnIndex;
            this.ColumnName = columnName;
            this.DataType = dataType;
            this.ColumnKey = columnName.Trim().ToLower();
        }
        /// <summary>
        /// Vizualizace
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            return "[" + this.ColumnIndex + "] " + this.ColumnName;
        }
        /// <summary>
        /// Reference na tabulku, k níž sloupec náleží
        /// </summary>
        public SimpleDataTable Table { get; private set; }
        /// <summary>
        /// Index sloupce
        /// </summary>
        public int ColumnIndex { get; private set; }
        /// <summary>
        /// Název sloupce, tak jak byl načten z databáze (může mít malé i velké znaky)
        /// </summary>
        public string ColumnName { get; private set; }
        /// <summary>
        /// Typ dat načtený z readeru. Obsahuje Type, který nereflektuje NULL stavy, tedy typicky Int32.
        /// </summary>
        public Type DataType { get; private set; }
        /// <summary>
        /// Název sloupce jako key (tj.vždy lower)
        /// </summary>
        protected string ColumnKey { get; private set; }
        #region Members of interface IIdKey
        int IIdKey.Id { get { return this.ColumnIndex; } }
        string IIdKey.Key { get { return this.ColumnKey; } }
        #endregion
    }
    #endregion
    #region class SimpleDataRowCollection : kolekce řádků
    /// <summary>
    /// SimpleDataRowCollection : kolekce řádků
    /// </summary>
    public class SimpleDataRowCollection : IEnumerable<SimpleDataRow>
    {
        /// <summary>
        /// Konstruktor
        /// </summary>
        public SimpleDataRowCollection()
        {
            this.Rows = new List<SimpleDataRow>();
        }
        /// <summary>
        /// Konstruktor
        /// </summary>
        /// <param name="rows"></param>
        public SimpleDataRowCollection(IEnumerable<SimpleDataRow> rows)
        {
            this.Rows = new List<SimpleDataRow>();
            if (rows != null)
                this.Rows.AddRange(rows);
        }
        /// <summary>
        /// Vizualizace
        /// </summary>
        /// <returns></returns>
        public override string ToString()
        {
            return "RowCollection of " + this.Count.ToString() + " rows";
        }
        private List<SimpleDataRow> Rows;
        /// <summary>
        /// Počet řádků v kolekci
        /// </summary>
        public int Count { get { return this.Rows.Count; } }
        /// <summary>
        /// Vrátí řádek podle jeho indexu
        /// </summary>
        /// <param name="index"></param>
        /// <returns></returns>
        public SimpleDataRow this[int index] { get { return this.Rows[index]; } }
        #region IEnumerable
        IEnumerator<SimpleDataRow> IEnumerable<SimpleDataRow>.GetEnumerator() { return this.Rows.GetEnumerator(); }
        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator() { return this.Rows.GetEnumerator(); }
        #endregion
    }
    #endregion
    #region class SimpleDataRow : Data jednoho řádku
    /// <summary>
    /// SimpleDataRow : Data jednoho řádku
    /// </summary>
    public class SimpleDataRow
    {
        /// <summary>
        /// Konstruktor
        /// </summary>
        /// <param name="table"></param>
        /// <param name="reader"></param>
        internal SimpleDataRow(SimpleDataTable table, SqlDataReader reader)
        {
            int columnCount = table.ColumnCount;
            this.ParentTable = table;
            this.NestedTables = null;
            this.Data = new object[columnCount];
            reader.GetValues(this.Data);
            for (int columnIndex = 0; columnIndex < columnCount; columnIndex++)
            {
                if (reader.IsDBNull(columnIndex))
                    this.Data[columnIndex] = null;
            }
        }
        /// <summary>
        /// Tabulka, která vlastní tento řádek
        /// </summary>
        public SimpleDataTable ParentTable { get; private set; }
        /// <summary>
        /// Podřízené tabulky
        /// </summary>
        public List<SimpleDataTable> NestedTables { get; set; }
        /// <summary>
        /// Data řádku bez jakýchkoli úprav
        /// </summary>
        public object[] Values { get { return this.Data.ToArray(); } }
        /// <summary>
        /// Počet hodnot řádku
        /// </summary>
        protected int ValueCount { get { return this.Data.Length; } }
        /// <summary>
        /// Interní ostré úložiště hodnot řádku
        /// </summary>
        protected object[] Data;
        /// <summary>
        /// Vrací obsah daného sloupce z tohoto řádku, pro daný název sloupce
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public T Get<T>(string columnName)
        {
            return this.Get<T>(this.ParentTable.GetColumn(columnName).ColumnIndex);
        }
        /// <summary>
        /// Vrací obsah daného sloupce z tohoto řádku, pro daný index sloupce
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="columnIndex"></param>
        /// <returns></returns>
        public T Get<T>(int columnIndex)
        {
            if (columnIndex < 0 || columnIndex >= this.ValueCount)
                throw new IndexOutOfRangeException("Column index " + Data.ToString() + " is out of range, ValueCount = " + this.ValueCount.ToString());

            object value = this.Data[columnIndex];

            if (value == null) return default(T);
            if (value is T) return (T)value;
            if (System.Convert.IsDBNull(value)) return default(T);

            // Konverze:
            if (typeof(bool).Equals(typeof(T)))
                return (T)((object)System.Convert.ToBoolean(value));
            if (typeof(byte).Equals(typeof(T)))
                return (T)((object)System.Convert.ToByte(value));
            if (typeof(DateTime).Equals(typeof(T)))
                return (T)((object)System.Convert.ToDateTime(value));
            if (typeof(decimal).Equals(typeof(T)))
                return (T)((object)System.Convert.ToDecimal(value));
            if (typeof(double).Equals(typeof(T)))
                return (T)((object)System.Convert.ToDouble(value));
            if (typeof(char).Equals(typeof(T)))
                return (T)((object)System.Convert.ToChar(value));
            if (typeof(Int16).Equals(typeof(T)))
                return (T)((object)System.Convert.ToInt16(value));
            if (typeof(Int32).Equals(typeof(T)))
                return (T)((object)System.Convert.ToInt32(value));
            if (typeof(Int64).Equals(typeof(T)))
                return (T)((object)System.Convert.ToInt64(value));
            if (typeof(sbyte).Equals(typeof(T)))
                return (T)((object)System.Convert.ToSByte(value));
            if (typeof(Single).Equals(typeof(T)))
                return (T)((object)System.Convert.ToSingle(value));
            if (typeof(string).Equals(typeof(T)))
                return (T)((object)System.Convert.ToString(value));
            if (typeof(UInt16).Equals(typeof(T)))
                return (T)((object)System.Convert.ToUInt16(value));
            if (typeof(UInt32).Equals(typeof(T)))
                return (T)((object)System.Convert.ToUInt32(value));
            if (typeof(UInt64).Equals(typeof(T)))
                return (T)((object)System.Convert.ToUInt64(value));

            if (typeof(byte[]).Equals(typeof(T)))
                return (T)(value);
            if (typeof(System.Drawing.Image).Equals(typeof(T)))
                return (T)_GetAsImage(value);

            throw new InvalidCastException("Unable to convert type " + this.ParentTable.GetColumn(columnIndex).DataType.Name + " to type " + typeof(T).Name + ", in SimpleTable.");
        }
        private object _GetAsImage(object value)
        {
            byte[] content = value as byte[];
            if (content == null) return null;
            using (System.IO.MemoryStream ms = new System.IO.MemoryStream(content))
            {
                return System.Drawing.Image.FromStream(ms);
            }
        }
    }
    #endregion
    #region Testy
    internal static class SimpleDataTableTest
    {
        internal static void TestMemory()
        {
            string[] selects = new string[]
            {
                "SELECT TOP 500 * FROM lcs.subjekty",
                "select top 5000 * from lcs.product_order order by cislo_subjektu",
                "select * from lcs.objekt_ucetnipohyb",
                "SELECT * FROM lcs.externi_soubor_data where cislo_nonsubjektu = 4"
            };

            // TestMemoryDataTable(selects);
            TestMemorySimple(selects);

            // Co se týká rychlosti a spotřeby paměti, 
            //    na vzorku dat (3 tabulky: 50 rows * 11 columns + 15 rows * 55 columns + 26117 rows * 57 columns = 1,490,044 values):
            // 1) System.Data.DataTable : spotřeba paměti 35 MB, rychlost 1,1 sekundy
            // 2) SimpleData            : spotřeba paměti 25 MB, rychlost 0,5 sekundy
            // Poznámka: kopie dat do TXT souboru zabere cca 9 MB.
        }
        static void TestMemoryDataTable(string[] selects)
        {
            MessageBox.Show("Before DataTable");

            System.Diagnostics.Process i = System.Diagnostics.Process.GetCurrentProcess();
            Int64 sizeB1 = i.PagedMemorySize64;
            Int64 sizeB2 = i.PrivateMemorySize64;

            Noris.Schedule.Support.Steward.ConnectToProfile(Noris.Schedule.Support.Steward.AppConfig.DbProfileList[0]);

            System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
            sw.Start();

            int rows = 0;
            List<System.Data.DataTable> tables = new List<System.Data.DataTable>();
            foreach (string select in selects)
            {
                System.Data.DataTable table = Noris.Schedule.Support.Sql.Db_Layer.GetDataTable(select);
                rows += table.Rows.Count;
                tables.Add(table);
            }

            sw.Stop();
            decimal time = (decimal)sw.ElapsedTicks / (decimal)System.Diagnostics.Stopwatch.Frequency;

            i = System.Diagnostics.Process.GetCurrentProcess();
            Int64 sizeA1 = i.PagedMemorySize64;
            Int64 sizeA2 = i.PrivateMemorySize64;

            MessageBox.Show("After. DeltaMemory=" + (sizeA1 - sizeB1).ToString() + ".  RowCount=" + rows.ToString() + ".  Time = " + time.ToString("####0.000") + " sec");
            GC.Collect();
            GC.Collect();
        }
        static void TestMemorySimple(string[] selects)
        {
            MessageBox.Show("Before TableSimple");


            System.Diagnostics.Process i = System.Diagnostics.Process.GetCurrentProcess();
            Int64 sizeB1 = i.PagedMemorySize64;
            Int64 sizeB2 = i.PrivateMemorySize64;

            Noris.Schedule.Support.Steward.ConnectToProfile(Noris.Schedule.Support.Steward.AppConfig.DbProfileList[0]);

            System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
            sw.Start();

            int rows = 0;
            int items = 0;
            List<SimpleDataTable> tables = new List<SimpleDataTable>();
            foreach (string select in selects)
            {
                SimpleDataTable table = SimpleDataTable.Load(select);
                tables.Add(table);
                rows += table.RowCount;
                items += table.RowCount * table.ColumnCount;
            }

            sw.Stop();
            decimal time = (decimal)sw.ElapsedTicks / (decimal)System.Diagnostics.Stopwatch.Frequency;

            SimpleDataTable table1 = tables[tables.Count - 1];
            foreach (SimpleDataRow row in table1.Rows)
            {
                foreach (SimpleDataColumn col in table1.Columns)
                {
                    object value = row.Get<object>(col.ColumnIndex);
                    if (value is byte[])
                    {
                        System.Drawing.Image img = row.Get<System.Drawing.Image>(col.ColumnIndex);
                    }
                }
                break;
            }


            i = System.Diagnostics.Process.GetCurrentProcess();
            Int64 sizeA1 = i.PagedMemorySize64;
            Int64 sizeA2 = i.PrivateMemorySize64;

            MessageBox.Show("After. DeltaMemory=" + (sizeA1 - sizeB1).ToString() + ".  RowCount=" + rows.ToString() + ".   ItemCount=" + items.ToString() + "; time=" + time.ToString("####0.000") + " sec");
            GC.Collect();
            GC.Collect();
        }
    }
    #endregion
}
